Portfolio Build Runbook — Two Pipelines, One Data Story

Goal: two portfolio dashboards, both end-to-end from a real ERP (Business Central / CRONUS), on two stacks that prove different things:

  • Microsoft-native (Fabric): signals Microsoft-stack depth to your agency targets.
  • Lean (BigQuery/DuckDB + dbt): proves data-engineering / reproducible workflows.

“Done” is not a running system — it’s reproducible + legible: a public GitHub repo (dbt + extract code), a dbt lineage graph, the Power BI reports (embedded), and a short walkthrough. Everything must outlive the trials.

Two clocks to respect: Business Central trial = 30 days, Fabric trial = 60 days. Pull data out of BC early; the warehouse + models persist after it lapses.

1. Accounts to create (Day 1)

Account Why Cost
Microsoft 365 tenant (EU region) — via M365 Developer Program or a Business Central trial signup Your new, non-Russian tenant; host for BC + Fabric Free (dev program E5 is renewable)
Business Central trial (CRONUS preloaded) The real ERP source, seeded Sales/Supply/Finance Free, 30 days
Fabric trial (F4→F64, 1 TB OneLake) MS-native pipeline Free, 60 days, no card
BigQuery sandbox Lean warehouse Free, no card
GitHub repo (AxesAccess) The portfolio artifact itself Free
Optional: Shopify partner dev store / HubSpot free 2nd source → multi-source story Free

Do the M365 tenant + BC trial first — the 30-day BC clock is the binding constraint.

2. Data foundation — real schema, synthetic depth

Source of truth: Business Central CRONUS, pulled via the BC API v2.0 (OData) endpoints. By domain:

  • Sales: salesInvoices, salesInvoiceLines, customers, items
  • Supply: itemLedgerEntries, items, purchaseInvoices, vendors, stock/inventory
  • Finance: generalLedgerEntries, accounts (chart of accounts), budgets, dimensions
  • Shared dims: customers, vendors, items, dimensions, a date table

Reality check: CRONUS data is small and clustered around a single work-date — great for proving schema + connectivity, too thin for forecasting or trend visuals. So:

Hybrid approach: use CRONUS for the real schema and connectivity proof, then augment with synthetic history (same customers/items/accounts, extended 24–36 months, realistic seasonality + noise) in the warehouse layer. The forecasts and cohorts need depth CRONUS doesn’t have.

I can generate that synthetic augmentation (Python, matched to the CRONUS keys) — see §8.

3. Pipeline A — Microsoft-native (Fabric), inside the 60-day trial

  1. Start the Fabric trial → create workspace “Frequentist Demo” → create a Lakehouse.
  2. Ingest BC → Lakehouse (bronze): Dataflow Gen2 against the BC API/OData entities above (or the native Business Central → Fabric link if enabled on your tenant). One table per entity, raw.
  3. Transform (silver → gold): Fabric notebook (PySpark or Spark SQL) or Dataflow — clean to silver, then build the gold star schema: dim_customer, dim_item, dim_date, dim_account, fct_sales, fct_inventory, fct_gl. Append the synthetic history here.
  4. Semantic model on gold → Power BI report. Build the Finance dashboard here (best CFO fit, and it shows Fabric end-to-end).
  5. Capture: screenshot the pipeline + lineage; publish the report.

Keep Fabric to one dashboard. It’s the “range” proof, not the whole estate — don’t burn the clock rebuilding everything twice.

4. Pipeline B — Lean (BigQuery/DuckDB + dbt), no clock pressure

  1. Extract BC → raw: a small Python script (use dlt or plain requests) pulls the BC API entities into a BigQuery raw dataset (or local DuckDB). Reuse the same synthetic augmentation.
  2. dbt project (structure in §5): sources → staging → intermediate → marts, with tests and dbt docs generate for the lineage graph (portfolio gold — it shows the engineering).
  3. Power BI import from BigQuery (native connector) or DuckDB via ODBC → build the Sales and Supply dashboards.

5. dbt project layout

frequentist_analytics/
├── dbt_project.yml
├── README.md                      # architecture diagram + how to run
├── profiles.example.yml
├── models/
│   ├── staging/
│   │   ├── _bc__sources.yml        # BC raw tables + freshness
│   │   ├── stg_bc__customers.sql
│   │   ├── stg_bc__items.sql
│   │   ├── stg_bc__sales_invoice_lines.sql
│   │   ├── stg_bc__item_ledger_entries.sql
│   │   ├── stg_bc__gl_entries.sql
│   │   ├── stg_bc__accounts.sql
│   │   └── stg_bc__budgets.sql
│   ├── intermediate/
│   │   ├── int_sales_enriched.sql          # lines + customer + item + date
│   │   ├── int_inventory_movements.sql     # item ledger → on-hand, turns
│   │   └── int_gl_with_dimensions.sql      # gl + account + dimension
│   └── marts/
│       ├── core/
│       │   ├── dim_customer.sql
│       │   ├── dim_item.sql
│       │   ├── dim_date.sql
│       │   └── dim_account.sql
│       ├── sales/
│       │   ├── fct_sales.sql
│       │   └── sales_kpis.sql               # revenue, margin, RFM inputs
│       ├── supply/
│       │   ├── fct_inventory.sql
│       │   └── supply_kpis.sql              # turns, days cover, ABC/XYZ, stockout risk
│       └── finance/
│           ├── fct_gl.sql
│           └── finance_kpis.sql             # P&L vs budget, variance, cash
├── tests/                          # singular tests (e.g. GL balances to zero)
├── macros/
└── seeds/                          # synthetic augmentation CSVs (or generated upstream)

Tests to include (they read as rigor on the repo): unique/not_null on all keys, relationships from facts to dims, accepted_values on status fields, and one singular test that GL debits = credits — a genuine finance-integrity check that signals you think like an analyst, not a chart-builder.

6. Dashboard ↔︎ source mapping

Dashboard Source entities Marts Signature analytical element
Finance (Fabric) generalLedgerEntries, accounts, budgets, dimensions fct_gl, finance_kpis P&L actual-vs-budget variance waterfall + cash forecast with interval
Sales (lean) salesInvoiceLines, customers, items fct_sales, sales_kpis, dim_customer Revenue forecast (confidence band) + RFM/cohort segmentation
Supply (lean) itemLedgerEntries, items, purchaseInvoices fct_inventory, supply_kpis ABC/XYZ + safety-stock / stockout-risk model

7. Persistence / capture checklist (do this AS you build, not after)

8. Timeline (fits the 30/60-day clocks)

  • Week 1: M365 tenant + BC trial → pull CRONUS via API → BigQuery raw + dbt staging. Generate synthetic augmentation. (BC clock started; data now safely extracted.)
  • Week 2: dbt intermediate + marts + tests + docs → Sales & Supply in Power BI (lean).
  • Week 3: Fabric trial → Finance end-to-end in Fabric → capture everything.
  • Week 4: embed on the site, swap the three reference tiles, record the walkthrough.

BC can lapse after Week 1 (data’s out); Fabric after Week 3 (artifacts captured). Total cash cost: €0.

9. What I can generate for you next

  • The synthetic augmentation dataset (Python generator: customers, items, 24–36 months of sales + inventory + GL with seasonality, matched to CRONUS keys) — drop-in seeds.
  • The dbt scaffold — dbt_project.yml, sources.yml, and the staging + marts models above as real SQL, ready to dbt build.
  • The BC extract script (Python, BC API v2.0 → BigQuery/DuckDB).
  • A README architecture diagram (mermaid).

Say which and I’ll write them into a repo layout you can push straight to GitHub.

Back to top